﻿Option Explicit On
Option Strict On

Imports System.Net
Imports System.IO
Imports System.Web
Imports System.Text.RegularExpressions
Imports System.Runtime.InteropServices

Public Class frmMain

#Region "Constants"

    Private Const mcintColName As Integer = 1               'Column in excel
    Private Const mcintColAddr As Integer = 2
    Private Const mcintColPhone As Integer = 3
    Private Const mcintColAdminPhone As Integer = 4
    Private Const mcintColFax As Integer = 5
    Private Const mcintColEmail As Integer = 6
    Private Const mcintColDirector As Integer = 7
    Private Const mcintColAntenna As Integer = 8
    Private Const mcintColInfor As Integer = 9
    Private Const mcintColTechnique As Integer = 10
    Private Const mcintColCommercial As Integer = 11
    Private Const mcintColCategory As Integer = 12
    Private Const mcintColFrequency As Integer = 13

    Private Const mcstrRegexAddr As String = "Adresse :"
    Private Const mcstrRegexPhone As String = "Téléphone antenne :"
    Private Const mcstrRegexAdminPhone As String = "Téléphone administratif :"
    Private Const mcstrRegexFax As String = "Télécopie :"
    Private Const mcstrRegexEmail As String = "E-mail :"
    Private Const mcstrRegexDirector As String = "Directeur / Président :"
    Private Const mcstrRegexAntenna As String = "Antenne :"
    Private Const mcstrRegexInfor As String = "Information :"
    Private Const mcstrRegexTechnique As String = "Technique :"
    Private Const mcstrRegexCommercial As String = "Commercial :"
    Private Const mcstrRegexCategory As String = "Catégorie :"
    Private Const mcstrRegexFrequency As String = "Fréquence :"

#End Region
   

    'const string table_pattern = "<table.*?>(.*?)</table>";
    'const string tr_pattern = "<tr>(.*?)</tr>";
    'const string td_pattern = "<td.*?>(.*?)</td>";
    'const string a_pattern = "<a href=\"(.*?)\"></a>";
    'const string b_pattern = "<b>(.*?)</b>";
    '<h1[^>]*?>(?<TagText>.*?)</h1>

    Private mlstField As List(Of String)
    Private mlstData As List(Of stExtractData)
    Private mblnClosable As Boolean

    Private Structure stExtractData
        Dim strName As String
        Dim strAddress As String
        Dim strPhone As String
        Dim strAdminPhone As String
        Dim strFax As String
        Dim strEmail As String
        Dim strDirector As String
        Dim strAntenna As String
        Dim strInformation As String
        Dim strTechnique As String
        Dim strCommercial As String
        Dim strCategory As String
        Dim strFrequency As String
    End Structure

    Private Function xInit() As Boolean
        xInit = False
        Try
            mlstField = New List(Of String)
            mlstField.Add(mcstrRegexAddr)
            mlstField.Add(mcstrRegexPhone)
            mlstField.Add(mcstrRegexAdminPhone)
            mlstField.Add(mcstrRegexFax)
            mlstField.Add(mcstrRegexEmail)
            mlstField.Add(mcstrRegexDirector)
            mlstField.Add(mcstrRegexAntenna)
            mlstField.Add(mcstrRegexInfor)
            mlstField.Add(mcstrRegexTechnique)
            mlstField.Add(mcstrRegexCommercial)
            mlstField.Add(mcstrRegexCategory)
            mlstField.Add(mcstrRegexFrequency)

            Return True
        Catch ex As Exception
            clsCommon.fncShowErr("xInit" & ex.Message)
        End Try
    End Function

    Private Sub frmMain_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            xInit()
        Catch ex As Exception
            clsCommon.fncShowErr("frmMain_Load" & ex.Message)
        End Try
    End Sub

    Private Sub btnExtract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExtract.Click
        Try
            mlstData = New List(Of stExtractData)
            barProgress.Value = 0
            barProgress.Visible = True
            btnExtract.Enabled = False

            If (txtFrom.Value > txtTo.Value) Then
                clsCommon.fncShowWarning("Start value must be smaller than end value.", txtFrom)
                Exit Sub
            End If

            If Not File.Exists(Environment.CurrentDirectory & "\template\template.xlt") Then
                clsCommon.fncShowWarning("Template file does not exist.")
                Exit Sub
            End If

            mblnClosable = False
            xProcess()

        Catch ex As Exception
            clsCommon.fncShowErr("btnExtract_Click" & ex.Message)
        Finally
            btnExtract.Enabled = True
            lblStatus.Text = "Done!"
            barProgress.Value = 100
            barProgress.Visible = False
            mblnClosable = True
        End Try
    End Sub

    Private Function xProcess() As Boolean
        xProcess = False
        Try
            'http request and get all value
            Dim strURL As String
            Dim intFrom As Integer
            Dim intTo As Integer
            Dim strTempContent As String
            Dim lstTable As List(Of String)
            Dim stData As stExtractData
            Dim intCurrentProgess As Integer = 1

            strURL = txtURL.Text
            intFrom = CInt(txtFrom.Value)
            intTo = CInt(txtTo.Value)

            For i As Integer = intFrom To intTo

                lblStatus.Text = "Getting data id = " & i
                strTempContent = xGetPageSource(strURL & i)
                lstTable = xGetTables(strTempContent)

                stData = New stExtractData()
                xGetContent(stData, lstTable)
                mlstData.Add(stData)
                barProgress.Value = xGetProgressPercent(intCurrentProgess, intFrom, intTo)
                intCurrentProgess += 1
                Application.DoEvents()
            Next

            'export to excel
            Dim objExcel As clsExcel = New clsExcel()
            Dim intRow As Integer = 2

            If Not objExcel.fncCreateXlsApp() Then
                clsCommon.fncShowWarning("Could not create Excel instance. Please install MS Excel on your computer.")
                Exit Function
            End If

            objExcel.fncOpenBook(Environment.CurrentDirectory & "\template\template.xlt")
            objExcel.fncOpenSheet(1)

            For i As Integer = 0 To mlstData.Count - 1
                lblStatus.Text = "Export data item = " & i
                objExcel.fncSetCellValue(mcintColName, intRow, mlstData(i).strName)
                objExcel.fncSetCellValue(mcintColAddr, intRow, mlstData(i).strAddress)
                objExcel.fncSetCellValue(mcintColPhone, intRow, mlstData(i).strPhone)
                objExcel.fncSetCellValue(mcintColAdminPhone, intRow, mlstData(i).strAdminPhone)
                objExcel.fncSetCellValue(mcintColFax, intRow, mlstData(i).strFax)
                objExcel.fncSetCellValue(mcintColEmail, intRow, mlstData(i).strEmail)
                objExcel.fncSetCellValue(mcintColDirector, intRow, mlstData(i).strDirector)
                objExcel.fncSetCellValue(mcintColAntenna, intRow, mlstData(i).strAntenna)
                objExcel.fncSetCellValue(mcintColInfor, intRow, mlstData(i).strInformation)
                objExcel.fncSetCellValue(mcintColTechnique, intRow, mlstData(i).strTechnique)
                objExcel.fncSetCellValue(mcintColCommercial, intRow, mlstData(i).strCommercial)
                objExcel.fncSetCellValue(mcintColCategory, intRow, mlstData(i).strCategory)
                objExcel.fncSetCellValue(mcintColFrequency, intRow, mlstData(i).strFrequency)
                intRow += 1
                barProgress.Value = xGetProgressPercent(intCurrentProgess, intFrom, intTo)
                intCurrentProgess += 1
                Application.DoEvents()
            Next

            objExcel.fncDisplay()
            objExcel.fncExit(False)

            Return True
        Catch ex As Exception
            clsCommon.fncShowErr("xProcess" & ex.Message)
        End Try
    End Function


    Private Function xGetPageSource(ByVal strURL As String) As String

        xGetPageSource = ""

        Try
            xGetPageSource = New WebClient().DownloadString(strURL)
            xGetPageSource = HttpUtility.HtmlDecode(xGetPageSource)
            xGetPageSource = xGetPageSource.Replace(vbCr, "")
            xGetPageSource = xGetPageSource.Replace(vbLf, "")

        Catch ex As Exception
            clsCommon.fncShowErr("xGetPageSource" & ex.Message)
        End Try

    End Function

    Private Function xGetTables(ByVal strSource As String) As List(Of String)

        Dim lstResult As List(Of String) = New List(Of String)
        Dim objRegex As Regex
        Dim objMatches As MatchCollection

        Try
            objRegex = New Regex("(?i)<table.*?>(.*?)</table>")
            objMatches = objRegex.Matches(strSource)

            For i As Integer = 0 To objMatches.Count - 1
                lstResult.Add(objMatches.Item(i).Value)
            Next

        Catch ex As Exception
            clsCommon.fncShowErr("xGetTables" & ex.Message)
        Finally
            objRegex = Nothing
            objMatches = Nothing
        End Try

        Return lstResult

    End Function

    Private Function xGetRows(ByVal strSource As String) As List(Of String)

        Dim lstResult As List(Of String) = New List(Of String)
        Dim objRegex As Regex
        Dim objMatches As MatchCollection

        Try
            objRegex = New Regex("(?i)<tr>(.*?)</tr>")
            objMatches = objRegex.Matches(strSource)

            For i As Integer = 0 To objMatches.Count - 1
                lstResult.Add(objMatches.Item(i).Value)
            Next

        Catch ex As Exception
            clsCommon.fncShowErr("xGetRows" & ex.Message)
        Finally
            objRegex = Nothing
            objMatches = Nothing
        End Try

        Return lstResult

    End Function

    Private Function xGetCells(ByVal strSource As String) As List(Of String)

        Dim lstResult As List(Of String) = New List(Of String)
        Dim objRegex As Regex
        Dim objMatch As MatchCollection

        Try
            Dim strValue As String

            objRegex = New Regex("(?i)<td.*?>(?<TagText>.*?)</td>")
            objMatch = objRegex.Matches(strSource)

            For i As Integer = 0 To objMatch.Count - 1
                strValue = objMatch(i).Groups("TagText").ToString().Trim()
                lstResult.Add(strValue.Replace("<BR>", vbCrLf))
            Next

        Catch ex As Exception
            clsCommon.fncShowErr("xGetCells" & ex.Message)
        Finally
            objRegex = Nothing
            objMatch = Nothing
        End Try

        Return lstResult

    End Function

    Private Function xGetContent(ByRef stData As stExtractData, ByVal lstTable As List(Of String)) As Boolean

        xGetContent = True

        Try
            Dim lstRows As List(Of String)
            Dim lstCells As List(Of String)
            Dim strCellVal As String

            'get name on table 0
            stData.strName = xGetTitle(lstTable(0))

            'get other infor on table 1
            lstRows = xGetRows(lstTable(1))

            'loop for field
            For i As Integer = 0 To mlstField.Count - 1
                'loop for row
                For j As Integer = 0 To lstRows.Count - 1
                    'if there is field string in row
                    If lstRows(j).Contains(mlstField(i)) Then
                        lstCells = xGetCells(lstRows(j))
                        strCellVal = ""
                        If lstCells.Count >= 2 Then strCellVal = lstCells(1)

                        Select Case i
                            Case 0 : stData.strAddress = strCellVal
                            Case 1 : stData.strPhone = strCellVal
                            Case 2 : stData.strAdminPhone = strCellVal
                            Case 3 : stData.strFax = strCellVal
                            Case 4 : stData.strEmail = xGetEmail(strCellVal)
                            Case 5 : stData.strDirector = strCellVal
                            Case 6 : stData.strAntenna = strCellVal
                            Case 7 : stData.strInformation = strCellVal
                            Case 8 : stData.strTechnique = strCellVal
                            Case 9 : stData.strCommercial = strCellVal
                        End Select

                        Exit For
                    End If
                Next
            Next

            'get Category and Frequency on table 3
            lstRows = xGetRows(lstTable(2))

            'loop for field
            For i As Integer = 10 To mlstField.Count - 1
                'loop for row
                For j As Integer = 0 To lstRows.Count - 1
                    'if there is field string in row
                    If lstRows(j).Contains(mlstField(i)) Then
                        lstCells = xGetCells(lstRows(j))
                        strCellVal = ""
                        If lstCells.Count >= 2 Then strCellVal = lstCells(1)

                        Select Case i
                            Case 10 : stData.strCategory = xRemoveAtag(strCellVal)
                            Case 11 : stData.strFrequency = strCellVal
                        End Select

                        Exit For
                    End If
                Next
            Next

            Return True

        Catch ex As Exception
            clsCommon.fncShowErr("xGetContent" & ex.Message)
        End Try

    End Function

    Private Function xGetTitle(ByVal strSource As String) As String

        xGetTitle = ""
        Dim objRegex As Regex
        Dim objMatch As Match

        Try
            Dim intBrIndex As Integer

            objRegex = New Regex("(?i)<h1[^>]*?>(?<TagText>.*?)</h1>")
            objMatch = objRegex.Match(strSource)
            xGetTitle = objMatch.Groups("TagText").ToString()

            intBrIndex = xGetTitle.IndexOf("<BR>")
            If intBrIndex > 0 Then xGetTitle = xGetTitle.Substring(0, intBrIndex)

        Catch ex As Exception
            clsCommon.fncShowErr("xGetTitle" & ex.Message)
        Finally
            objRegex = Nothing
            objMatch = Nothing
        End Try

    End Function

    Private Function xRemoveAtag(ByVal strSource As String) As String
        xRemoveAtag = ""
        Dim objRegex As Regex
        Dim objMatch As Match
        Try

            objRegex = New Regex("(?i)<a[^>]*?>(?<TagText>.*?)</a>")
            objMatch = objRegex.Match(strSource)
            xRemoveAtag = objMatch.Groups("TagText").ToString()

        Catch ex As Exception
            clsCommon.fncShowErr("xRemoveAtag" & ex.Message)
        Finally
            objRegex = Nothing
            objMatch = Nothing
        End Try
    End Function

    Private Function xGetEmail(ByVal strSource As String) As String
        xGetEmail = ""
        Dim objRegex As Regex
        Dim objMatch As Match

        Try
            Dim strTemp As String

            'get image link
            objRegex = New Regex("(?i)src=""(?<TagText>.*?)""")
            objMatch = objRegex.Match(strSource)
            strTemp = objMatch.Groups("TagText").ToString()
            strTemp = strTemp.Substring(strTemp.IndexOf("=") + 1)
            If String.IsNullOrEmpty(strTemp) Then Exit Function

            'reverse string to get email
            For i As Integer = strTemp.Length - 1 To 0 Step -1
                xGetEmail &= strTemp(i)
            Next

            xGetEmail = xGetEmail.Replace("04%", "@")

        Catch ex As Exception
            clsCommon.fncShowErr("xGetEmail" & ex.Message)
        Finally
            objRegex = Nothing
            objMatch = Nothing
        End Try
    End Function

    Private Function xGetProgressPercent(ByVal intCurrent As Integer, ByVal intStart As Integer, ByVal intEnd As Integer) As Integer
        xGetProgressPercent = 0
        Try
            xGetProgressPercent = CInt(intCurrent / ((intEnd - intStart) * 2) * 100)
            If xGetProgressPercent > 100 Then Return 100
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Private Sub frmMain_FormClosing(sender As System.Object, e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        Try
            If Not mblnClosable Then e.Cancel = True

        Catch ex As Exception
            clsCommon.fncShowErr("frmMain_FormClosing" & ex.Message)
        End Try
    End Sub
End Class
